﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace nkstdgia.common.ThamDinhGia.DataAccess
{
    public class SqlHelper
    {
        public SqlHelper()
        {
        }

        #region private methods

        private static object CheckValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            return value;
        }

        private static SqlConnection GetTransactionSqlConnection(string connectionString)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            return connection;
        }

        private static SqlCommand CreateCommand(SqlConnection connection, CommandType commandType, string commandText)
        {
            if ((connection != null) && (connection.State == ConnectionState.Closed))
            {
                connection.Open();
            }
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandText = commandText;
                command.CommandType = commandType;
                return command;
            }
        }

        private static SqlCommand CreateCommand(SqlConnection connection, CommandType commandType, string commandText, params object[] values)
        {
            if ((connection != null) && (connection.State == ConnectionState.Closed))
            {
                connection.Open();
            }
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandText = commandText;
                command.CommandType = commandType;
                if ((values == null) || (values.Length == 0))
                {
                    for (int j = 0; j < command.Parameters.Count; j++)
                    {
                        command.Parameters[j].Value = DBNull.Value;
                    }
                    return command;
                }
                command.Parameters.AddRange(values);
                return command;
            }
        }

        private static DataSet CreateDataSet(SqlCommand command)
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
            {
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                return dataSet;
            }
        }

        private static DataTable CreateDataTable(SqlCommand command)
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
            {
                DataTable dataTable = new DataTable();
                adapter.Fill(dataTable);
                return dataTable;
            }
        }

        #endregion private methods

        #region public methods

        public static DataSet ExecuteDataSet(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteDataSet(connectionString, commandType, commandText, (DbParameter[])null);
        }

        public static DataSet ExecuteDataSet(string connectionString, CommandType commandType, string commandText, params DbParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = CreateCommand(connection, commandType, commandText, parameters))
                {
                    return CreateDataSet(command);
                }
            }
        }

        public static void ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
        {
            ExecuteNonQuery(connectionString, commandType, commandText, (DbParameter[])null);
        }

        public static void ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params DbParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = CreateCommand(connection, commandType, commandText, parameters))
                {
                    command.ExecuteNonQuery();
                }
                return;
            }
        }

        public static IDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteReader(connectionString, commandType, commandText, (DbParameter[])null);
        }

        public static IDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params DbParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            using (SqlCommand command = CreateCommand(connection, commandType, commandText, parameters))
            {
                return command.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }

        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params DbParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            using (SqlCommand command = CreateCommand(connection, commandType, commandText, parameters))
            {
                return command.ExecuteScalar();
            }
        }

        #endregion public methods
    }
}